﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DBUnitility
{
    public class SqlHeplerT
    {
        private static string connstring = ConfigurationManager.ConnectionStrings["connStringT"].ToString();//连接字符串

        #region 执行格式化SQL语句
        /// <summary>
        /// 执行增、删、改操作
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public static int Update(string sql)
        {
            SqlConnection conn = new SqlConnection(connstring); //创建连接
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                //把错误信息写入日志
                WriteErrorLog("执行public static int Update(string sql)发生错误，具体信息：" + ex.Message);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 执行单一结果
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public static object GetSingalResult(string sql)
        {
            SqlConnection conn = new SqlConnection(connstring); //创建连接
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                //把错误信息写入日志
                WriteErrorLog("执行public static object GetSingalResult(string sql)发生错误，具体信息：" + ex.Message);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 执行返回数据集查询
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public static SqlDataReader GetReader(string sql)
        {
            SqlConnection conn = new SqlConnection(connstring); //创建连接
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                //把错误信息写入日志
                WriteErrorLog("执行public static SqlDataReader GetReader(string sql)发生错误，具体信息：" + ex.Message);
                throw ex;
            }
        }
        /// <summary>
        /// 执行返回数据集查询
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public static DataSet GetDataSet(string sql)
        {
            SqlConnection conn = new SqlConnection(connstring); //创建连接
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                //把错误信息写入日志
                WriteErrorLog("执行public static DataSet GetDataSet(string sql)发生错误，具体信息：" + ex.Message);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 同时执行多条查询语句，并降结果填充到对应的DataTable中，读取可以根据表名称访问DataTable
        /// </summary>
        /// <param name="sqlDic">使用HashTable类型的泛型集合封装一条SQL语句和表名称</param>
        /// <returns>返回一个若干数据表的数据集</returns>
        public static DataSet GetDataSet(Dictionary<string, string> sqlDic)
        {
            SqlConnection conn = new SqlConnection(connstring); //创建连接
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                foreach (string tableName in sqlDic.Keys)
                {
                    cmd.CommandText = sqlDic[tableName];
                    da.Fill(ds, tableName);
                }
                return ds;
            }
            catch (Exception ex)
            {
                //把错误信息写入日志
                WriteErrorLog("执行public static DataSet GetDataSet(Dictionary<string ,string>sqlDic)发生错误，具体信息：" + ex.Message);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 基于事务执行多条增删改语句
        /// </summary>
        /// <param name="sqlList">SQL语句集合</param>
        /// <returns>是否执行成功</returns>
        public static bool UpdateByTran(List<string> sqlList)
        {
            SqlConnection conn = new SqlConnection(connstring); //创建连接
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            try
            {
                conn.Open();
                cmd.Transaction = conn.BeginTransaction();//开启事务
                //执行多条SQL语句
                foreach (string item in sqlList)
                {
                    cmd.CommandText = item;
                    cmd.ExecuteNonQuery();
                }
                cmd.Transaction.Commit();//提交事务（并清除事务）
                return true;
            }
            catch (Exception ex)
            {
                if (cmd.Transaction != null)
                {
                    cmd.Transaction.Rollback();//回滚事务
                }
                //把错误信息写入日志
                WriteErrorLog("执行 public static bool UpdateByTran(List<string> sqlList)发生错误，具体信息：" + ex.Message);
                throw new Exception("执行事务 public static bool UpdateByTran(List<string> sqlList)办法错误，详细信息：" + ex.Message);
            }
            finally
            {
                if (cmd.Transaction != null) cmd.Transaction = null;
                conn.Close();
            }
        }
        #endregion

        #region 执行带参数SQL语句
        /// <summary>
        /// 执行增、删、改操作
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public static int Update(string sql, SqlParameter[] param)
        {
            SqlConnection conn = new SqlConnection(connstring); //创建连接
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddRange(param);
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                //把错误信息写入日志
                WriteErrorLog("执行 public static int Update(string sql,SqlParameter[] param)发生错误，具体信息：" + ex.Message);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 执行单一结果
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public static object GetSingalResult(string sql, SqlParameter[] param)
        {
            SqlConnection conn = new SqlConnection(connstring); //创建连接
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddRange(param);
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                //把错误信息写入日志
                WriteErrorLog("执行public static object GetSingalResult(string sql)发生错误，具体信息：" + ex.Message);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 执行返回数据集查询
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public static SqlDataReader GetReader(string sql, SqlParameter[] param)
        {
            SqlConnection conn = new SqlConnection(connstring); //创建连接
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddRange(param);
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                //把错误信息写入日志
                WriteErrorLog("执行public static SqlDataReader GetReader(string sql)发生错误，具体信息：" + ex.Message);
                throw ex;
            }
        }

        /// <summary>
        /// 启用事务提交多条带参数的SQL语句
        /// </summary>
        /// <param name="mainSql">主表SQL语句</param>
        /// <param name="mainParam">主SQL语句对应的参数</param>
        /// <param name="detailSql">明细表SQL语句</param>
        /// <param name="detailParam">明细表SQL语句对应的参数数组集合</param>
        /// <returns>返回事务是否执行成功</returns>
        public static bool UpdateByTran(string mainSql, SqlParameter[] mainParam, string detailSql, List<SqlParameter[]> detailParam)
        {
            SqlConnection conn = new SqlConnection(connstring); //创建连接
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            try
            {
                conn.Open();
                cmd.Transaction = conn.BeginTransaction();//开启事务
                if (mainSql != null && mainSql.Length != 0)
                {
                    cmd.CommandText = mainSql;
                    cmd.Parameters.AddRange(mainParam);
                    cmd.ExecuteNonQuery();
                }
                //执行多条SQL语句
                foreach (SqlParameter[] param in detailParam)
                {
                    cmd.CommandText = detailSql;
                    cmd.Parameters.Clear(); //必须清除以前的参数
                    cmd.Parameters.AddRange(param);
                    cmd.ExecuteNonQuery();
                }
                cmd.Transaction.Commit();//提交事务（并清除事务）
                return true;
            }
            catch (Exception ex)
            {
                if (cmd.Transaction != null)
                {
                    cmd.Transaction.Rollback();//回滚事务
                }
                string errorInfo = "public static bool UpdateByTran(string mainSql,SqlParameter[] mainParam,string detailSql,List<SqlParameter[]> detailParam)" + ex.Message;
                //把错误信息写入日志
                WriteErrorLog(errorInfo);
                throw new Exception(errorInfo);
            }
            finally
            {
                if (cmd.Transaction != null) cmd.Transaction = null;
                conn.Close();
            }
        }
        /// <summary>
        /// 启用事务调用带参数的存储过程
        /// </summary>
        /// <param name="procedureName">存储过程名称</param>
        /// <param name="paramArray">存储过程参数数组集合</param>
        /// <returns>返回基于事务的存储过程调用是否成功</returns>
        public static bool UpdateByTran(string procedureName, List<SqlParameter[]> paramArray)
        {
            SqlConnection conn = new SqlConnection(connstring); //创建连接
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            try
            {
                conn.Open();
                cmd.CommandType = CommandType.StoredProcedure;//必须声明当前操作是调用存储过程
                cmd.CommandText = procedureName;
                cmd.Transaction = conn.BeginTransaction();//开启事务             
                //执行多条SQL语句
                foreach (SqlParameter[] param in paramArray)
                {
                    cmd.Parameters.Clear(); //必须清除以前的参数
                    cmd.Parameters.AddRange(param);
                    cmd.ExecuteNonQuery();
                }
                cmd.Transaction.Commit();//提交事务（并清除事务）
                return true;
            }
            catch (Exception ex)
            {
                if (cmd.Transaction != null)
                {
                    cmd.Transaction.Rollback();//回滚事务
                }
                string errorInfo = " public static bool UpdateByTran(string procedureName,List<SqlParameter[]> paramArray)" + ex.Message;
                //把错误信息写入日志
                WriteErrorLog(errorInfo);
                throw new Exception(errorInfo);
            }
            finally
            {
                if (cmd.Transaction != null) cmd.Transaction = null;
                conn.Close();
            }
        }
        #endregion

        #region 调用存储过程SQL语句
        public static int UpdateByProcedure(string procedureName, SqlParameter[] param)
        {
            SqlConnection conn = new SqlConnection(connstring); //创建连接
            SqlCommand cmd = new SqlCommand(procedureName, conn);
            cmd.CommandType = CommandType.StoredProcedure;//使用存储过程
            try
            {
                conn.Open();
                if (param != null)
                {
                    cmd.Parameters.AddRange(param);
                }
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                //把错误信息写入日志
                WriteErrorLog("执行 public static int Update(string sql,SqlParameter[] param)发生错误，具体信息：" + ex.Message);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        public static SqlDataReader GetReaderByProcedure(string procedureName, SqlParameter[] param)
        {
            SqlConnection conn = new SqlConnection(connstring); //创建连接
            SqlCommand cmd = new SqlCommand(procedureName, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                conn.Open();
                if (param != null)
                {
                    cmd.Parameters.AddRange(param);
                }

                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                //把错误信息写入日志
                WriteErrorLog("执行public static SqlDataReader GetReader(string sql)发生错误，具体信息：" + ex.Message);
                throw ex;
            }
        }
        public static DataSet GetDataSetByProcedure(string procedureName, SqlParameter[] param)
        {
            SqlConnection conn = new SqlConnection(connstring); //创建连接
            SqlCommand cmd = new SqlCommand(procedureName, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                if (param != null)
                {
                    cmd.Parameters.AddRange(param);
                }
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                //把错误信息写入日志
                WriteErrorLog("执行public static DataSet GetDataSet(string sql)发生错误，具体信息：" + ex.Message);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion

        #region 获取服务器时间、把错误信息写入日志
        public static DateTime GetDBServerTime()
        {
            string sql = "select getdate()";
            return (DateTime)GetSingalResult(sql);
        }
        /// <summary>
        /// 将错误信息写入日志
        /// </summary>
        /// <param name="msg">错误信息</param>
        private static void WriteErrorLog(string msg)
        {
            FileStream fs = new FileStream("ProjectDemo.log", FileMode.Append); //追加方式
            StreamWriter sw = new StreamWriter(fs);
            sw.WriteLine("【" + DateTime.Now.ToString() + "】错误信息：" + msg); //写入数据
            sw.Close();//关闭读写器
            fs.Close();//关闭文件
        }
        #endregion
    }
}
